import win32com.client
from win32com.client import Dispatch, constants
import os

app_path = "D:\\Eigene Datein\\Python\\TIGS DB\\"
source_path = app_path + "files\\"

class ExcelWorkbook:
    """An Excel workbook object."""
    def __init__(self, filename=None):
        from win32com.client import gencache
        # Excel 2003
        #gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 4)
        # Excel 2007
        gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 6)
        # Start excel
        self.xlApp = Dispatch('Excel.Application')
        self.xlApp.CutCopyMode = False

        if filename and os.path.exists(filename):
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
        self.filename = filename

    def save(self, newfilename=None):
        """Save excel file to."""
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):
        """Close workbook."""
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp

    def show(self):
        """Show excel file."""
        self.xlApp.Visible = 1

    def hide(self):
        """Hide excel file."""
        self.xlApp.Visible = 0

    def newSheet(self, sheet):
        """Add new worksheet."""
        try:    # fails if sheet already exists
            self.xlBook.Sheets(sheet).Name == sheet
        except:
            self.xlSheet = self.xlBook.Worksheets.Add()
            self.xlSheet.Name = sheet

    def setRange(self, sheet, topRow, leftCol, data):
        """Insert a 2d array starting at given location.
        topRow = 2, leftCol = 1 -> A2"""
        bottomRow = topRow + len(data) - 1
        rightCol = leftCol + len(data[0]) - 1

        sht = self.xlBook.Worksheets(sheet)
        sht.Range(
             sht.Cells(topRow, leftCol),
             sht.Cells(bottomRow, rightCol)
             ).Value = data

    def setNumberFormat(self, sheet, topRow, leftCol, bottomRow, rightCol, format):
        sht = self.xlBook.Worksheets(sheet)
        sht.Range(
             sht.Cells(topRow, leftCol),
             sht.Cells(bottomRow, rightCol)
             ).NumberFormat = format


    def setOutliers(self, sheet):
        """Read outlier coordinates from a file and paint background red."""
        f_outlier_id = open(source_path + 'outlier_id.txt', 'r')
        outlier = f_outlier_id.readlines()
        sht = self.xlBook.Worksheets(sheet)

        for out in outlier:
            out = out.strip()
            out = out.split('\t')
            out = int(out[0]), int(out[1])
            sht.Cells(int(out[0]), int(out[1])).Interior.ColorIndex = 3

    def newChartInSheet(self, sheet, num = 1, left = 10, width = 600,
                         top = 50, height = 450, type = ''):
        """Add a new BarChart into the current worksheet."""

        chart_type = 0x33    # BarChart
        self.xlBook.Worksheets('Statistic').Activate()
        try:
            self.selectSheet(sheet)
        except: # Sheet doesn't exist so create it
            self.newSheet(sheet)
        try :
            self.xlBook.Sheets(sheet).ChartObjects(num).Activate    # Already exists
        except:
            self.xlChart = self.xlBook.Sheets(sheet).ChartObjects().Add(
                                     Left = left, Width = width, Top = top,
                                     Height = height)
            self.xlChart.Chart.ChartType = chart_type

    def addXYChartSeries(self, sheet, topRow, bottomRow, xCol, yCol,
                         series_name="", chart_sheet="", chart_num = 1,
                         color = 1, style = 'line',
                         title = "", xlabel = "", ylabel = "", errorbars= {}):

        """Add data to the Barchart."""

        if not chart_sheet:
            chart_sheet = sheet

        # New series.
        sht = self.xlBook.Worksheets(sheet)

        se = self.xlChart.Chart.SeriesCollection().NewSeries()
        se.Values = sht.Range(sht.Cells(topRow,    yCol),
                               sht.Cells(bottomRow, yCol))
        se.XValues = sht.Range(sht.Cells(topRow,    xCol),
                                sht.Cells(bottomRow, xCol))

        if series_name:
            se.Name = series_name

        # Chart properties
        cht = self.xlBook.Sheets(chart_sheet).ChartObjects(chart_num).Chart

        cht.HasLegend = False
        cht.PlotArea.Interior.ColorIndex = constants.xlNone

        # Chart Title
        if title:
            cht.HasTitle = True
            cht.ChartTitle.Caption = title
            cht.ChartTitle.Font.Name = 'Arial'
            cht.ChartTitle.Font.Size = 10
            cht.ChartTitle.Font.Bold = False
        # X axis labels
        if xlabel:
            cht.Axes(constants.xlCategory).HasTitle = True
            cht.Axes(constants.xlCategory).AxisTitle.Caption = xlabel
            cht.Axes(constants.xlCategory).AxisTitle.Font.Name = 'Arial'
            cht.Axes(constants.xlCategory).AxisTitle.Font.Size = 10
            cht.Axes(constants.xlCategory).AxisTitle.Font.Bold = False
            cht.Axes(constants.xlCategory).TickLabels.Orientation = constants.xlUpward
            cht.Axes(constants.xlCategory).TickLabelPosition = constants.xlLow
            cht.Axes(constants.xlCategory).HasMajorGridlines = False
            cht.Axes(constants.xlCategory).TickLabelSpacing = 1


        # Y axis labels
        if ylabel:
            cht.Axes(constants.xlValue).HasTitle = True
            cht.Axes(constants.xlValue).AxisTitle.Caption = ylabel
            cht.Axes(constants.xlValue).AxisTitle.Font.Name = 'Arial'
            cht.Axes(constants.xlValue).AxisTitle.Font.Size = 10
            cht.Axes(constants.xlValue).AxisTitle.Font.Bold = False
            cht.Axes(constants.xlCategory).HasMajorGridlines = False


        #Add Errorbars:
        # R and C must be replaced with Z and S if German version!!!
        # Example "=test!Z2S18:Z11S18"
        # This problem is solved > Office 2007
        try:
            amount = "".join(["=", chart_sheet, "!",
                                            "Z",
                        str(errorbars['amount'][0]),
                                                                    "S",
                        str(errorbars['amount'][2]),
                                                                    ":",
                                                                    "Z",
                        str(errorbars['amount'][1]),
                                                                    "S",
                        str(errorbars['amount'][2])])

            se.ErrorBar(Direction = constants.xlY,
                         Include = constants.xlErrorBarIncludeBoth,
                         Type = constants.xlErrorBarTypeCustom,
                         Amount = amount, MinusValues = amount)

        except:
            amount = "".join(["=", chart_sheet, "!",
                                            "R",
                        str(errorbars['amount'][0]),
                                                                    "C",
                        str(errorbars['amount'][2]),
                                                                    ":",
                                                                    "R",
                        str(errorbars['amount'][1]),
                                                                    "C",
                        str(errorbars['amount'][2])])

            se.ErrorBar(Direction = constants.xlY,
                         Include = constants.xlErrorBarIncludeBoth,
                         Type = constants.xlErrorBarTypeCustom,
                         Amount = amount, MinusValues = amount)



        se.ErrorBars.EndStyle = constants.xlCap
        se.ErrorBars.Border.LineStyle = constants.xlContinuous
        se.ErrorBars.Border.ColorIndex = color
        se.ErrorBars.Border.Weight = constants.xlHairline



##
#### For testing
#a = ExcelWorkbook()
#s = a.newSheet('Statistic')
#
## Add data
#data = [['ID', '%', '%', '%', '%', '%', 'log2', 'log2', 'log2', 'log2', 'log2', 'nalimov', 'nalimov', 'nalimov', 'nalimov', 'nalimov', 'mean', 'std', 'sem', 'ttest', 'ttest'], ['TA30N', '101.01', '110.06', '42.14', '162.79', '', '0.0144981269994', '0.13829023378', '-1.24673778073', '0.703012079251', '', '0.0698539627394', '0.141576927826', '1.44520606524', '1.37348310015', '', '-0.0977343351755', '0.822538238389', '0.411269119194', '0.658123445312', '0.658123445312'], ['TA36', '39.6', '47.82', '16.25', '13.81', '', '-1.33642766458', '-1.06431396484', '-2.62148837675', '-2.85621477529', '', '0.697962941534', '1.25878946934', '0.895139178195', '1.06161323268', '', '-1.96961119536', '0.900276666142', '0.450138333071', '0.0340102654729', '0.0340102654729'], ['HW07G11_007 (Snap34)', '156.3', '187.59', '291.56', '', '', '0.644317778338', '0.907582923083', '1.54379280554', '', '', '0.960232008349', '0.419031296108', '1.37926330446', '', '', '1.03189783565', '0.462444028239', '0.266992184189', '0.0379674128479', '0.0379674128479'], ['TA30N', '98.99', '104.69', '106.51', '100.0', '88.37', '-0.0146453038274', '0.0661236424653', '0.0909888884148', '0.0', '-0.178371410756', '0.114090223206', '0.786622065261', '1.07421791877', '0.0455096735226', '1.79225943435', '-0.0071808367407', '0.105385408057', '0.0471297872506', '0.00345855834661', '0.00345855834661'], ['HW07G11_027 (Snap34)', '185.03', '195.17', '229.35', '307.77', '123.21', '0.887759202322', '0.964731310188', '1.19755090742', '1.62185261126', '0.301119353151', '0.381524244114', '0.213875785312', '0.351235094555', '1.64778264281', '1.40361770794', '0.994602676868', '0.481626777535', '0.21539004287', '0.00386866676007', '0.00386866676007'], ['HT08E03_TA30N (LSDB1)', '102.73', '44.31', '58.65', '36.38', '73.15', '0.0388575500204', '-1.17429576806', '-0.769796986634', '-1.4587825519', '-0.451070230524', '1.68965386099', '0.797610629234', '0.187077031326', '1.13523485737', '0.430268656936', '-0.76301759742', '0.590254071871', '0.26396964574', '0.153602786059', '0.153602786059'], ['HO09K14_TA30 (WIR1a)', '65.6', '175.79', '75.71', '128.88', '', '-0.608232280044', '0.813853003583', '-0.401444226676', '0.366028399157', '', '1.03778797783', '1.45385288234', '0.809178402588', '0.393113498085', '', '0.0425512240049', '0.663373963762', '0.331686981881', '0.378886919751', '0.378886919751'], ['HW07G11_TA30N (Snap34)', '70.06', '134.75', '123.56', '', '', '-0.513337106747', '0.430285272978', '0.305211776078', '', '', '1.39557330556', '0.895981914087', '0.499591391474', '', '', '0.0740533141029', '0.512524608957', '0.295906220948', '0.334564772965', '0.334564772965'], ['TA30N', '85.25', '151.35', '48.84', '', '', '-0.230228260751', '0.597888675165', '-1.03386489456', '', '', '0.233243648056', '1.32459452673', '1.09135087868', '', '', '-0.222068160049', '0.815907389666', '0.471064351058', '0.885281311947', '0.885281311947'], ['HvARF1 in TA30N', '115.7', '115.05', '6.2', '228.87', '30.03', '0.210388864445', '0.202260983562', '-4.01158797428', '1.19452836858', '-1.73552361999', '0.210710013492', '0.202424380778', '1.18510042071', '1.65330240472', '0.881336378273', '-0.827986675535', '2.07298948621', '0.92706908156', '0.598795309735', '0.598795309735']]
#a.setRange('Statistic', 1, 1, data)
#chart = a.newChartInSheet('Statistic')
#errorbars = {'amount':(2 ,11, 18)}
#a.addXYChartSeries('Statistic', 2, 11, 1, 17, chart_sheet=chart, errorbars=errorbars, title = 'TIGS Statistic',
#                   ylabel='Mean effect log2', xlabel='IDs')
#
#a.setOutliers('Statistic')
## Save
#a.save('test2.xls')




